package dao;

import bean.PageRequest;
import bean.Result;
import bean.Student;
import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李夏
 * @version 1.0
 */
//管理员获取某年级在校离校情况(2020-2023),2020为大四
public class StuInfoMapper {

    public static List<Integer> queryOnLeaveSchool(Integer grade, List<Integer> list) throws SQLException {
        Integer femaleOn=0,femaleLeave=0,maleOn=0,maleLeave=0;
        Integer begin=12000*(grade-1)+1;
        Integer end=12000*grade;
        femaleOn=StuInfoMapper.queryFemaleOn(begin,end);
        femaleLeave=StuInfoMapper.queryFemaleLeave(begin,end);
        maleOn=StuInfoMapper.queryMaleOn(begin,end);
        maleLeave=StuInfoMapper.queryMaleLeave(begin,end);
        list.add(femaleOn);
        list.add(femaleLeave);
        list.add(maleOn);
        list.add(maleLeave);
        System.out.println(femaleLeave+ femaleOn+ maleLeave+ maleOn);
        return list;
    }

    private static Integer queryMaleLeave(Integer begin, Integer end) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(distinct stu.stu_id) as total_students  \n" +
                "from stu  \n" +
                "join hol on stu.stu_id =hol.stu_id  \n" +
                "where stu.delete_flag=0 and stu.sex=1  \n" +
                "  and hol.hol_status ='离校'  \n" +
                "and stu.stu_id between ? and ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, begin);
        preparedStatement.setInt(2, end);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_students");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;

    }

    private static Integer queryMaleOn(Integer begin, Integer end) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(distinct stu.stu_id) as total_students  \n" +
                "from stu  \n" +
                "join hol on stu.stu_id =hol.stu_id  \n" +
                "where stu.delete_flag=0 and stu.sex=1  \n" +
                "  and hol.hol_status ='在校'  \n" +
                "and stu.stu_id between ? and ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, begin);
        preparedStatement.setInt(2, end);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_students");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }

    private static Integer queryFemaleLeave(Integer begin, Integer end) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(distinct stu.stu_id) as total_students  \n" +
                "from stu  \n" +
                "join hol on stu.stu_id =hol.stu_id  \n" +
                "where stu.delete_flag=0 and stu.sex=2  \n" +
                "  and hol.hol_status ='离校'  \n" +
                "and stu.stu_id between ? and ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, begin);
        preparedStatement.setInt(2, end);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {

            count = resultSet.getInt("total_students");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }

    private static Integer queryFemaleOn(Integer begin, Integer end) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(distinct stu.stu_id) as total_students  \n" +
                "from stu  \n" +
                "join hol on stu.stu_id =hol.stu_id  \n" +
                "where stu.delete_flag=0 and stu.sex=2  \n" +
                "  and hol.hol_status ='在校'  \n" +
                "and stu.stu_id between ? and ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, begin);
        preparedStatement.setInt(2, end);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_students");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//获取学生总数
    public static Integer queryStuAllCount() throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_students  \n"+
                "from stu  \n"+
                "where delete_flag=0";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_students");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
    //管理员/宿管获取某楼栋的学生总数

    public static Integer getStuOneBuild(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_students  \n"+
                "from stu  \n"+
                "where delete_flag=0 and build_id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, buildId);

        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_students");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
    //管理员/宿管获取某楼栋某寝室学生信息
    public static List<Student> getStuOneBuildName(Integer buildId, Integer dormitory) throws SQLException {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from stu where build_id=? and dormitory=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dormitory);
            resultSet = preparedStatement.executeQuery();
            List<Student> students = new ArrayList<>();
            while (resultSet.next()){
                Student student=new Student();
                student.setPhone(resultSet.getString("phone"));
                student.setStuId(resultSet.getInt("stu_id"));
                student.setStuName(resultSet.getString("stu_name"));
                student.setPassword(resultSet.getString("password"));
                student.setBuildId(resultSet.getInt("build_id"));
                student.setDormitory(resultSet.getInt("dormitory"));
                student.setBed(resultSet.getInt("bed"));
                student.setCollege(resultSet.getString("college"));
                student.setProfession(resultSet.getString("profession"));
                students.add(student);
            }
            return students;


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
